﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DAL;
using Entities;

namespace BLL
{
    public class Command
    {
        public static bool siteAdminLogin(String user, String pwd)
        {
            String sqlAdminUserLogin = "SELECT username FROM AdminUser WHERE username='"+user+"' and pwd='"+pwd+"'";

            DataSet SiteAdmin = DAL.DBHelper.getDataSet(sqlAdminUserLogin);

            if (SiteAdmin == null || SiteAdmin.Tables[0].Rows.Count != 1)
            {
                return false;
            }

            return true;
        }

        public static bool RegisterProvider(ref Owner owner)
        {
            bool ret = false;

            String sqlRetisterOwner = "INSERT INTO Owner(owner_name,owner_pwd,owner_phone,owner_idcard)"
            + " VALUES('" + owner.Name + "','" + owner.Pwd + "','" + owner.Phone + "','" + owner.IdCard + "')"
            + ";SELECT SCOPE_IDENTITY() as 'last_insert_id'";

            DataSet ds = DAL.DBHelper.getDataSet(sqlRetisterOwner);

            owner.Id = Convert.ToInt16(ds.Tables[0].Rows[0].ItemArray[0].ToString());
            ret = owner.Id > 0;

            return ret;
        }
        public static bool OwnerLogin(ref Owner owner)
        {
            bool ret = false;
            String sqlQueryOwner =
                "SELECT owner_id, owner_phone, owner_idcard FROM Owner where owner_name='" + owner.Name + "' and owner_pwd='" + owner.Pwd + "'";

            SqlDataReader ownerReader = DAL.DBHelper.getDataReader(sqlQueryOwner);
            if (null == ownerReader)
                return false;

            if (ownerReader.Read())
            {
                owner.Id = ownerReader.GetInt16(0);
                owner.Phone = ownerReader.GetString(1);
                owner.IdCard = ownerReader.GetString(2);
                ret = true;
            }
            ownerReader.Close();

            return ret;
        }
        public static bool GetOwner(ref Owner owner)
        {
            bool ret = false;
            String sqlQueryOwner =
                "SELECT owner_name,owner_phone,owner_idcard FROM Owner where owner_id='" + owner.Id + "'";

            SqlDataReader ownerReader = DAL.DBHelper.getDataReader(sqlQueryOwner);
            if (null == ownerReader)
                return false;

            if (ownerReader.Read())
            {
                owner.Name = ownerReader.GetString(0);
                owner.Phone = ownerReader.GetString(1);
                owner.IdCard = ownerReader.GetString(2);
                ret = true;
            }

            ownerReader.Close();

            return ret;
        }
        public static bool updateOwner(String username, String pwd, String phone, String idcard)
        {
            String sqlString = "UPDATE Owner set owner_phone='" + phone + "',owner_idcard='" + idcard + "' ";

            if (pwd.Length > 0)
                sqlString += "set owner_pwd='" + pwd + "'";

            sqlString += "WHERE owner_name='" + username + "'";

            int rowCount = DAL.DBHelper.executeSql(sqlString);

            if (rowCount == 1)
                return true;
            else
                return false;
        }
        public static List<Restaurant> GetRestaurantsByOwner(Int16 ownerId)
        {
            String criteria = " Restaurant.owner_id=" + ownerId;

            return GetRestaurants(criteria);
        }
        public static List<Restaurant> GetRestaurants(String criteria)
        {
            String select = "SELECT ";
            return GetRestaurants(criteria, select);
        }
        public static List<Restaurant> GetRestaurants(String criteria, String select)
        {
            List<Restaurant> restaurants = new List<Restaurant>();

            String queryCmd = select
               + " restaurant_id,restaurant_name,restaurant_tel,restaurant_mobile,restaurant_advertise"
               + ",substring(convert(varchar,restaurant_begin,8), 0, 6) restaurant_begin,substring(convert(varchar,restaurant_end,8), 0, 6) restaurant_end"
               + ",Restaurant.score_id score_id,restaurant_open,restaurant_approved,restaurant_online,restaurant_selfm,restaurant_deadline"
               + ",Owner.owner_id owner_id, owner_name, owner_phone, owner_idcard"
               + ",Address.address_id, address_road, address_building, address_room, address_description"
               + ",District.district_id district_id, district_name, district_description, district_type, District.parent_id parent_district_id"
               + ",Delivery.delivery_id delivery_id, delivery_description"
               + ",Score.score_id score_id, score_good, score_middle, score_bad"
               + " FROM Restaurant JOIN Owner ON Restaurant.owner_id=Owner.owner_id"
               + " JOIN Address ON Address.address_id=Restaurant.address_id"
               + " JOIN District ON District.district_id=Address.district_id"
               + " JOIN Delivery ON Delivery.delivery_id=Restaurant.delivery_id"
               + " LEFT JOIN Score ON Score.score_id=Restaurant.score_id"
               + " WHERE " + criteria
               +" ORDER BY score_good DESC, score_bad ASC";

            SqlDataReader restaurantReader = DAL.DBHelper.getDataReader(queryCmd);
            if (null == restaurantReader)
                return null;

            while (restaurantReader.Read())
            {
                Restaurant restaurant = new Restaurant(restaurantReader.GetInt16(0));
                // Fill the data into the struct;
                restaurant.Name = restaurantReader.GetString(1);
                restaurant.Tel = restaurantReader.GetString(2);
                restaurant.Mobile = restaurantReader.GetString(3);
                restaurant.Advertise = restaurantReader.GetString(4);
                restaurant.Begin = restaurantReader.GetString(5);
                restaurant.End = restaurantReader.GetString(6);
                restaurant.Open = restaurantReader.GetBoolean(8);
                restaurant.Aproved = restaurantReader.GetBoolean(9);
                restaurant.Online = restaurantReader.GetBoolean(10);
                restaurant.SelfM = restaurantReader.GetBoolean(11);
                restaurant.Deadline = restaurantReader.GetDateTime(12);

                // Query Owner and Address info
                restaurant.Owner = new Owner();
                restaurant.Owner.Id = restaurantReader.GetInt16(13);
                restaurant.Owner.Name = restaurantReader.GetString(14);
                restaurant.Owner.Phone = restaurantReader.GetString(15);
                restaurant.Owner.IdCard = restaurantReader.GetString(16);

                restaurant.Address = new Address();
                restaurant.Address.Id = restaurantReader.GetInt16(17);
                //restaurant.Address.Road = restaurantReader.GetString(18);
                //restaurant.Address.Building = restaurantReader.GetInt16(19);
                //restaurant.Address.Room = restaurantReader.GetInt16(20);
                restaurant.Address.Description = restaurantReader.GetString(21);

                restaurant.Address.District = new District(restaurantReader.GetInt16(22));
                restaurant.Address.District.Name = restaurantReader.GetString(23);
                restaurant.Address.District.Description = restaurantReader.GetString(24);
                restaurant.Address.District.Type = restaurantReader.GetByte(25);
                //restaurant.Address.District.ParentId = restaurantReader.GetInt16(26);

                restaurant.Delivery = new Delivery();
                restaurant.Delivery.Id = restaurantReader.GetInt16(27);
                restaurant.Delivery.Description = restaurantReader.GetString(28);

                // The Score if available
                // If no relevant score created, create it.
                object value = restaurantReader.GetValue(29);
                if (value.ToString().Length > 0)
                {
                    Score score = new Score(restaurantReader.GetInt16(29));
                    score.Good = restaurantReader.GetInt16(30);
                    score.Middle = restaurantReader.GetInt16(31);
                    score.Bad = restaurantReader.GetInt16(32);
                    restaurant.Score = score;
                }
                else
                {
                    Int16 scoreId = BLL.Command.NewScore();
                    restaurant.SetScore(scoreId);
                }

                restaurants.Add(restaurant);
            }
            restaurantReader.Close();

            return restaurants;
        }
        public static Score GetScore(Int16 scoreId)
        {
            Score score = null;

            String sqlCmd = "SELECT score_good, score_middle, score_bad FROM Score WHERE score_id=" + scoreId;

            SqlConnection conn = DAL.DBHelper.Conn();
            DataSet ds = DAL.DBHelper.getDataSet(sqlCmd);

            if (ds != null)
            {
                score = new Score(scoreId);
                score.Good = Convert.ToInt16(ds.Tables[0].Rows[0].ItemArray[0].ToString());
                score.Middle = Convert.ToInt16(ds.Tables[0].Rows[0].ItemArray[1].ToString());
                score.Bad = Convert.ToInt16(ds.Tables[0].Rows[0].ItemArray[2].ToString());
            }

            return score;
        }
        public static List<Restaurant> GetRestaurantsByDistrict(Int16 districtId, Int16 topNum)
        {
            String select = "SELECT";
            if (topNum > 0)
                 select += " TOP " + topNum;

            String criteria = "Address.district_id=" + districtId + " AND restaurant_open=1";

            return GetRestaurants(criteria, select);
        }
        public static Restaurant GetRestaurant(Int16 restaurantId)
        {
            String criteria = " Restaurant.restaurant_id=" + restaurantId;

            List<Restaurant> restaurants = GetRestaurants(criteria);

            return restaurants[0];
        }
        public static List<Category> GetCategoriesByRestaurant(Int16 restaurantId)
        {
            List<Category> categories = new List<Category>();
            String sqlCmd =
                 "SELECT category_id, category_name, category_description"
            + " FROM Category"
            + " WHERE restaurant_id=" + restaurantId;

            DataSet ds = DAL.DBHelper.getDataSet(sqlCmd);
            for (int i=0; i<ds.Tables[0].Rows.Count; i++) 
            {
                Category category = new Category(Convert.ToInt16(ds.Tables[0].Rows[i].ItemArray[0].ToString()));
                category.Name = ds.Tables[0].Rows[i].ItemArray[1].ToString();
                category.Description = ds.Tables[0].Rows[i].ItemArray[2].ToString();
                categories.Add(category);
            }

            return categories;
        }
        public static DataSet GetDishesByRestaurant(Int16 restaurantId)
        {
            String sqlCmd =
                 "SELECT categorydish_id,convert(float,categorydish_price,6) as categorydish_price"
                + ",categorydish_available, categorydish_score,Dish.dish_id as dish_id,dish_name,dish_description"
                + ",Category.category_id category_id, category_name, category_description"
                + " FROM CategoryDish "
                + " JOIN Category ON Category.category_id=CategoryDish.category_id"
                + " JOIN Dish ON Dish.dish_id=CategoryDish.dish_id"
                + " WHERE Category.restaurant_id=" + restaurantId
                + " ORDER BY category_id ASC, categorydish_price ASC";

            return DAL.DBHelper.getDataSet(sqlCmd);
        }
        public static bool NewCategory(ref Category category)
        {
            if (category.RestaurantId == 0 || category.Name.Trim().Length == 0)
                return false;

            bool ret = false;

            String sqlCmd =
                 "INSERT INTO Category(category_name, category_description, restaurant_id)"
            + " VALUES('" + category.Name + "','" + category.Description + "'," + category.RestaurantId + ")"
            + ";SELECT SCOPE_IDENTITY() as 'last_insert_id'";

            DataSet ds = DAL.DBHelper.getDataSet(sqlCmd);
            if (null == ds)
                return false;

            category.Id = Convert.ToInt16(ds.Tables[0].Rows[0].ItemArray[0].ToString());
            ret = category.Id > 0;

            return ret;
        }
        public static Int16 DelCategory(Int16 categoryId)
        {
            //Debug.Assert(categoryId > 0);
            Int16 deletedNum = 0;

            String sqlCmd =
                 "DELETE FROM Category WHERE category_id="+categoryId
            + ";SELECT @@ROWCOUNT as 'affected_rows_num'";

            SqlDataReader reader = DAL.DBHelper.getDataReader(sqlCmd);

            if (reader.Read())
            {
                deletedNum = Convert.ToInt16(reader.GetValue(0).ToString());
            }

            reader.Close();

            return deletedNum;
        }

        public static Dish GetDishByName(String dishName)
        {
            Dish dish = null;

            String sqlCmd =
                 "SELECT dish_id,dish_description,dish_approved"
            + " FROM Dish"
            + " WHERE dish_name='" + dishName+"'";

            DataSet ds = DAL.DBHelper.getDataSet(sqlCmd);
            if (ds != null && ds.Tables[0].Rows.Count>0)
            {
                dish = new Dish();
                dish.Id = Convert.ToInt16(ds.Tables[0].Rows[0].ItemArray[0].ToString());
                dish.Description = ds.Tables[0].Rows[0].ItemArray[1].ToString();
                dish.Approved = Convert.ToBoolean(ds.Tables[0].Rows[0].ItemArray[2].ToString());
            }

            return dish;
        }
        public static bool UpdateRestaurant(ref Restaurant restaurant)
        {
            String sqlUpdateAddress = "UPDATE Address SET address_road='"+restaurant.Address.Road+"'"
                +", address_building="+restaurant.Address.Building
                +", address_room="+restaurant.Address.Room
                +", address_description='"+restaurant.Address.Description+"'"
                +", district_id="+restaurant.Address.District.Id
                +"  FROM Restaurant WHERE Restaurant.address_id=Address.address_id AND restaurant_id="+restaurant.Id;

            String sqlUpdateDelivery = "UPDATE Delivery SET delivery_description='" + restaurant.Delivery.Description + "'"
                + " WHERE delivery_id=" + restaurant.Delivery.Id;

            String sqlUpdateRestaurnt = "UPDATE Restaurant SET restaurant_name='" + restaurant.Name + "'"
                + ", restaurant_tel='" + restaurant.Tel + "'"
                + ", restaurant_mobile='" + restaurant.Mobile + "'"
                + ", restaurant_advertise='" + restaurant.Advertise + "'"
                + ", restaurant_begin='" + restaurant.Begin + "'"
                + ", restaurant_end='" + restaurant.End + "'"
                + ", restaurant_open=" + (restaurant.Open?"1":"0")
                + "  WHERE restaurant_id=" + restaurant.Id;

            ClearServedDistricts(restaurant.Id);
            AddServedDistrict(restaurant.Id, restaurant.ServedDistricts);

            if (DAL.DBHelper.executeSql(sqlUpdateAddress) > 0
                && DAL.DBHelper.executeSql(sqlUpdateDelivery) > 0
                && DAL.DBHelper.executeSql(sqlUpdateRestaurnt) > 0)
                return true;
            else
                return false;
        }
        public static bool NewRestaurant(ref Restaurant restaurant)
        {
            String sqlNewAddress = "INSERT INTO Address(address_description, district_id) VALUES('" + restaurant.Address.Description + "', " + restaurant.Address.District.Id + ")"
                                + "; SELECT SCOPE_IDENTITY() as 'last_insert_id', @@ROWCOUNT as 'affected_rows_num'";
            SqlDataReader addressReader = DAL.DBHelper.getDataReader(sqlNewAddress);
            if (addressReader.Read())
            {
                restaurant.Address.Id  = Convert.ToInt16(addressReader.GetValue(0).ToString());
            }
            addressReader.Close();
            if (restaurant.Address.Id == 0)
                return false;

            String sqlNewDelivery = "INSERT INTO Delivery(delivery_description) VALUES('" + restaurant.Delivery.Description + "')"
                                + "; SELECT SCOPE_IDENTITY() as 'last_insert_id', @@ROWCOUNT as 'affected_rows_num'";
            SqlDataReader deliveryReader = DAL.DBHelper.getDataReader(sqlNewDelivery);
            if (deliveryReader.Read())
            {
                restaurant.Delivery.Id = Convert.ToInt16(deliveryReader.GetValue(0).ToString());
            }
            deliveryReader.Close();
            if (restaurant.Delivery.Id == 0)
                return false;

            String sqlNewScore = "INSERT INTO Score(score_good) VALUES(0)"
                                + "; SELECT SCOPE_IDENTITY() as 'last_insert_id', @@ROWCOUNT as 'affected_rows_num'";
            SqlDataReader scoreReader = DAL.DBHelper.getDataReader(sqlNewScore);
            if (scoreReader.Read())
            {
                restaurant.Score.Id = Convert.ToInt16(scoreReader.GetValue(0).ToString());
            }
            scoreReader.Close();
            if (restaurant.Score.Id == 0)
                return false;

            String sqlNewRestaurant = "INSERT INTO Restaurant(restaurant_name, restaurant_tel, restaurant_mobile, restaurant_advertise"
                                + ", restaurant_begin, restaurant_end, restaurant_open, address_id, owner_id, delivery_id, score_id)"
                                + " VALUES('" + restaurant.Name + "','" + restaurant.Tel + "','" + restaurant.Mobile+"'" 
                                + ",'" + restaurant.Advertise + "','" + restaurant.Begin+"','" + restaurant.End+"'"
                                + ","+(restaurant.Open ? "1" : "0") +","+ restaurant.Address.Id + "," + restaurant.Owner.Id
                                + "," + restaurant.Delivery.Id
                                + "," + restaurant.Score.Id + ")"
                                + "; SELECT SCOPE_IDENTITY() as 'last_insert_id', @@ROWCOUNT as 'affected_rows_num'";
            SqlDataReader restaurantReader = DAL.DBHelper.getDataReader(sqlNewRestaurant);
            
            if (restaurantReader.Read())
            {
                restaurant.Id = Convert.ToInt16(restaurantReader.GetValue(0).ToString());
            }
            restaurantReader.Close();
            if (restaurant.Id == 0)
                return false;

            ClearServedDistricts(restaurant.Id);
            AddServedDistrict(restaurant.Id, restaurant.ServedDistricts);

            return true;
        }
        public static bool AddServedDistrict(Int16 restaurantId, List<District> districts)
        {
            String sqlCmd = "INSERT INTO ServedDistrict(restaurant_id,district_id)";

            for (int i = districts.Count - 1; i >= 0; --i)
            {
                sqlCmd += " SELECT " + restaurantId + ", " + districts[i].Id;
                if (i > 0)
                    sqlCmd += " UNION";
            }

            if (districts.Count > 0)
                return 0 < DAL.DBHelper.executeSql(sqlCmd);
            else
                return false;
        }
        public static void ClearServedDistricts(Int16 restaurantId)
        {
            String sqlCmd = "DELETE FROM ServedDistrict WHERE restaurant_id=" + restaurantId;

            DAL.DBHelper.executeSql(sqlCmd);
        }
        public static bool DelRestaurant(Int16 restaurantId)
        {
            // address
            // delivery
            // restaurant
            Restaurant restaurant = GetRestaurant(restaurantId);
            Debug.Assert(null != restaurant);

            String delRestaurant = "DELETE FROM Restaurant WHERE restaurant_id=" + restaurantId;
            bool ret = DAL.DBHelper.executeSql(delRestaurant) == 1;
            Debug.Assert(ret);

            if (null != restaurant.Address)
            {
                String delAddress = "DELETE FROM Address WHERE address_id=" + restaurant.Address.Id;
                ret = DAL.DBHelper.executeSql(delAddress) == 1;
                Debug.Assert(ret);
            }

            if (null != restaurant.Delivery)
            {
                String delDelivery = "DELETE FROM Delivery WHERE delivery_id=" + restaurant.Delivery.Id;
                ret = DAL.DBHelper.executeSql(delDelivery) == 1;
                Debug.Assert(ret);
            }

            return ret;
        }
        public enum DistrictType
        {
            ForConsumer = 1,
            ForRestaurant = 2,
            ForAll = 3
        }
        public static List<District> GetDistricts(String criteria)
        {
            String sqlCmd =
                    "SELECT district_id, district_name, district_description, district_type"
                 + " FROM District"
                 + " WHERE " + criteria;

            List<District> districts = new List<District>();

            DataSet ds = DAL.DBHelper.getDataSet(sqlCmd);
            for (int i =0; i<ds.Tables[0].Rows.Count; i++)
            {
                Int16 districtId = Convert.ToInt16(ds.Tables[0].Rows[i].ItemArray[0].ToString());
                String districtName = ds.Tables[0].Rows[i].ItemArray[1].ToString();
                String districtDescription = ds.Tables[0].Rows[i].ItemArray[2].ToString();
                Byte districtType = Convert.ToByte(ds.Tables[0].Rows[i].ItemArray[3].ToString());

                District district = new District(districtId);
                district.Name = districtName;
                district.Description = districtDescription;
                district.Type = districtType;

                districts.Add(district);
            }

            return districts;
        }
        public static List<District> GetDistricts(DistrictType type)
        {
            String criteria = "(district_type&" + (Int16)type + ")<>0";

            return GetDistricts(criteria);
        }
        public static District GetDistrict(Int16 districtId)
        {
            String criteria = "district_id=" + districtId;
            List<District> districts = GetDistricts(criteria);

            if (districts.Count == 1)
                return districts[0];
            else
                return null;
        }

        public enum CommentLevel
        {
            Good = 1,
            Middle = 2,
            Bad = 3
        }

        public static Int16 NewComment(Int16 scoreId, CommentLevel level, String content)
        {
            if (content.Length == 0)
                return 0;
             
            String sqlCmd = "INSERT INTO Comment(comment_level, comment_content, score_id)"
                    + " VALUES(" + (Int16)level + ",'" + content + "'," + scoreId + ")"
                    + "; SELECT SCOPE_IDENTITY() as 'last_insert_id', @@ROWCOUNT as 'affected_rows_num'";
            SqlDataReader commentReader = DAL.DBHelper.getDataReader(sqlCmd);
            Int16 lastInsertId = 0;
            if (commentReader.Read())
            {
                lastInsertId = Convert.ToInt16(commentReader.GetValue(0).ToString());
                Debug.Assert(lastInsertId > 0);
            }
            commentReader.Close();

            return lastInsertId;
        }

        public static bool Comment(Int16 scoreId, CommentLevel level, String content)
        {
            if (content.Length > 0)
                NewComment(scoreId, level, content);

            return IncrementScore(scoreId, level);
        }
        public static bool SetScoreForRestaurant(Int16 restaurantId, Int16 scoreId)
        {
            String sqlCmd = "UPDATE Restaurant SET score_id=" + scoreId + "WHERE restaurant_id=" + restaurantId;
            return DAL.DBHelper.executeSql(sqlCmd) > 0;
        }
        public static bool SetScoreForCategoryDish(Int16 categoryDishId, Int16 scoreId)
        {
            String sqlCmd = "UPDATE CategoryDish SET score_id=" + scoreId + "WHERE categorydish_id=" + categoryDishId;
            return DAL.DBHelper.executeSql(sqlCmd) > 0;
        }
        public static Int16 NewScore()
        {
            Int16 scoreId = 0;
            String sqlNew = "INSERT INTO Score(score_good) VALUES(0); SELECT SCOPE_IDENTITY() as 'last_insert_id'";

            SqlDataReader reader = DAL.DBHelper.getDataReader(sqlNew);
            if (reader.Read())
            {
                scoreId = Convert.ToInt16(reader.GetValue(0).ToString());
            }
            reader.Close();

            return scoreId;
        }
        public static bool IncrementScore(Int16 scoreId, CommentLevel level)
        {
            String sqlCmd = "UPDATE Score SET";
            switch (level)
            {
                case CommentLevel.Good:
                    sqlCmd += " score_good = score_good + 1";
                    break;
                case CommentLevel.Middle:
                    sqlCmd += " score_middle = score_middle + 1";
                    break;
                case CommentLevel.Bad:
                    sqlCmd += " score_bad = score_bad + 1";
                    break;
            }

            sqlCmd += " WHERE score_id=" + scoreId;


            return DAL.DBHelper.executeSql(sqlCmd) > 0;
        }

        public static DataSet getCategoryInfo(String categoryId)
        {
            String sqlGetCategoryInfo =
                "SELECT * FROM Category WHERE category_id=" + categoryId;

            DataSet ds = DAL.DBHelper.getDataSet(sqlGetCategoryInfo);

            if (ds == null || ds.Tables.Count != 1)
            {
                return null;
            }

            return ds;
        }
        public static DataSet GetCategoryDishesByCategory(Int16 categoryId)
        {
            String sqlCmd =
                "SELECT categorydish_id Id, convert(float,categorydish_price,6) as Price, categorydish_available Available, categorydish_score"
                + ",Dish.dish_id as DishId, dish_name Name, dish_description Description" 
                + " FROM CategoryDish JOIN Dish ON CategoryDish.dish_id=Dish.dish_id"
                + " WHERE category_id=" + categoryId
                + " AND categorydish_available=1"
                //+ " AND dish_approved=1"
                + " ORDER BY categorydish_score DESC, categorydish_price ASC";

            DataSet ds = DAL.DBHelper.getDataSet(sqlCmd);
            //while (reader.Read())
            //{
            //    CagetoryDish categoryDish = new CagetoryDish();

            //    categoryDish.Id = reader.GetInt16(0);
            //    categoryDish.Price = reader.GetDouble(1);
            //    categoryDish.Available = reader.GetBoolean(2);
            //    categoryDish.DishId = reader.GetInt16(4);
            //    categoryDish.Name = reader.GetString(5);

            //    categoryDishes.Add(categoryDish);
            //}
            //reader.Close();

            return ds;
        }

        public static bool NewDish(ref Dish dish)
        {
            bool ret = false;

            String sqlCmd =
                "INSERT INTO Dish(dish_name,dish_description) "
              + " VALUES('" + dish.Name + "','" + dish.Description + "')"
              + ";SELECT SCOPE_IDENTITY() AS 'last_insert_id'";

            SqlDataReader reader = DAL.DBHelper.getDataReader(sqlCmd);
            if (reader.Read())
            {
                dish.Id = Convert.ToInt16(reader.GetValue(0).ToString());
                ret = true;
            }
            reader.Close();
            
            return ret;
        }
        public static bool NewIngredient(ref Ingredient ingredient)
        {
            bool ret = false;

            String sqlCmd =
                "INSERT INTO Ingredient(ingredient_name,ingredient_description) "
              + " VALUES('" + ingredient.Name + "','" + ingredient.Description + "')"
              + ";SELECT SCOPE_IDENTITY() AS 'last_insert_id'";

            SqlDataReader reader = DAL.DBHelper.getDataReader(sqlCmd);
            if (reader.Read())
            {
                ingredient.Id = Convert.ToInt16(reader.GetValue(0).ToString());
                ret = ingredient.Id > 0;
            }
            reader.Close();

            return ret;
        }
        public static bool NewCategoryDish(ref CagetoryDish categoryDish)
        {
            bool ret = false;

            String sqlCmd =
                "INSERT INTO CategoryDish(categorydish_price,category_id,dish_id)"
              + " VALUES(" + categoryDish.Price + "," + categoryDish.CategoryId + "," + categoryDish.DishId + ")"
              + ";SELECT SCOPE_IDENTITY() AS 'last_insert_id'";

            SqlDataReader reader = DAL.DBHelper.getDataReader(sqlCmd);

            if (reader != null && reader.Read())
            {
                categoryDish.Id = Convert.ToInt16(reader.GetValue(0).ToString());
                ret = categoryDish.Id > 0;
                reader.Close();
            }

            return ret;
        }
        public static bool addDish2Restaurant(String restaurantId, String dishId)
        {
            String sqlAddDish2Restaurant =
                "INSERT INTO CategoryDish(restaurant_id,dish_id) " +
                "VALUES(" + restaurantId + "," + dishId + ")";

            if (1 != DAL.DBHelper.executeSql(sqlAddDish2Restaurant))
                return false;

            return true;
        }
        public static bool UpdateCategoryDish(ref CagetoryDish categoryDish)
        {
            String sqmCmd =
                "UPDATE CategoryDish SET categorydish_price=" + categoryDish.Price
                + ",categorydish_available=" + (categoryDish.Available?"1":"0")
              + ",category_id="+categoryDish.CategoryId
              + " WHERE categorydish_id=" + categoryDish.Id;

            if (1 != DAL.DBHelper.executeSql(sqmCmd))
                return false;

            return true;
        }
        public static bool UpdateCategory(ref Category category)
        {
            String sqmCmd =
                "UPDATE Category SET category_name='" + category.Name+"'"
              + ",category_description='" + category.Description + "'"
              + " WHERE category_id=" + category.Id;

            if (1 != DAL.DBHelper.executeSql(sqmCmd))
                return false;

            return true;
        }
        public static bool DelCategoryDish(Int16 categoryDishId)
        {
            String sqlDelCategoryDish =
                "DELETE FROM CategoryDish WHERE categorydish_id=" + categoryDishId;

            if (1 != DAL.DBHelper.executeSql(sqlDelCategoryDish))
                return false;

            return true;
        }
        public static DataSet getDishByName(String dishName)
        {
            String sqlGetDishId =
                "SELECT * from Dish " +
                "WHERE dish_name='" + dishName + "'";

            DataSet ds = DAL.DBHelper.getDataSet(sqlGetDishId);

            if (ds == null || ds.Tables.Count != 1)
            {
                return null;
            }

            return ds;
        }
        public static List<District> GetServedDistricts(Int16 restruantId)
        {
            String sqlCmd =
                "SELECT District.district_id district_id, district_name, district_description"
              + " FROM District JOIN ServedDistrict ON ServedDistrict.district_id=District.district_id"
              + " WHERE restaurant_id=" + restruantId;

            List<District> servedDistricts = new List<District>();

            DataSet ds = DAL.DBHelper.getDataSet(sqlCmd);
            for (int i=0; i<ds.Tables[0].Rows.Count; i++)
            {
                District district = new District(Convert.ToInt16(ds.Tables[0].Rows[i].ItemArray[0].ToString()));
                district.Name = (ds.Tables[0].Rows[i].ItemArray[1].ToString());
                district.Description = ds.Tables[0].Rows[i].ItemArray[2].ToString();
                servedDistricts.Add(district);
            }

            return servedDistricts;
        }
        public static SqlDataReader GetCategoryDishesByDishId(int dishId, int districtId, int consumerDistrictId)
        {
            String sqlGetCategoryDishSameList =
                "SELECT categorydish_id,dish_name,convert(float,categorydish_price,6) as categorydish_price, district_name"
                + ",Restaurant.restaurant_id restaurant_id,restaurant_name,restaurant_tel,restaurant_mobile,categorydish_score,address_description as restaurant_address"
                + " FROM CategoryDish Join Dish ON CategoryDish.dish_id=Dish.dish_id Join Category ON Category.category_id=CategoryDish.category_id"
                + " Join Restaurant ON Restaurant.restaurant_id=Category.restaurant_id"
                + " Join Address ON Address.address_id=Restaurant.address_id Join District ON District.district_id=Address.district_id"
                + " WHERE Dish.dish_id=" + dishId
                + " AND restaurant_open=1"
                + " AND categorydish_available=1";

            if (districtId > 0)
                sqlGetCategoryDishSameList += " AND Address.district_id=" + districtId;

            if (consumerDistrictId > 0)
                sqlGetCategoryDishSameList += " AND EXISTS "
                                                + "(" 
                                                + " SELECT district_id"
                                                + " FROM ServedDistrict"
                                                + " WHERE ServedDistrict.restaurant_id=Restaurant.restaurant_id"
                                                + " AND ServedDistrict.district_id=" + consumerDistrictId
                                                + ")";

            sqlGetCategoryDishSameList += " ORDER BY categorydish_score, categorydish_price";


            return DAL.DBHelper.getDataReader(sqlGetCategoryDishSameList);
        }
        public static DataSet getCategoryDishesWithSimilarName(String dishId, int districtId, int consumerDistrictId)
        {
            String sqlGetCategoryDishSameList =
                "SELECT categorydish_id,dish_name,convert(float,categorydish_price,6) as categorydish_price,restaurant_name,restaurant_tel," +
                "restaurant_mobile,categorydish_score,restaurant_address " +
                "FROM CategoryDish,Dish,Restaurant " +
                "WHERE CategoryDish.restaurant_id=Restaurant.restaurant_id AND restaurant_open=1 AND CategoryDish.dish_id=Dish.dish_id " +
                    " AND Dish.dish_id=(" +
                                        " SELECT dish_id FROM CategoryDish WHERE categorydish_id=" + dishId +
                                        ")";

            if (districtId > 0)
                sqlGetCategoryDishSameList += " AND district_id=" + dishId;

            if (consumerDistrictId > 0)
                sqlGetCategoryDishSameList += " AND " + consumerDistrictId.ToString() + " IN " +
                                                "(SELECT district_id " +
                                                "FROM ServedDistrict,CategoryDish " +
                                                "WHERE categorydish_id=" + dishId +
                                                " AND CategoryDish.restaurant_id=ServedDistrict.restaurant_id)";

            sqlGetCategoryDishSameList += " ORDER BY categorydish_score, categorydish_price";


            DataSet ds = DAL.DBHelper.getDataSet(sqlGetCategoryDishSameList);

            if (ds == null || ds.Tables.Count != 1)
            {
                return null;
            }

            return ds;
        }
        public static DataSet getCategoryDishesWithSimilarIngredients(String dishId, int districtId, int consumerDistrictId)
        {
            String sqlGetCategoryDishSameList =
                "SELECT categorydish_id,dish_name,convert(float,categorydish_price,6) as categorydish_price,restaurant_name,restaurant_tel," +
                "restaurant_mobile,categorydish_score,restaurant_address " +
                "FROM CategoryDish,Dish,Restaurant " +
                "WHERE CategoryDish.restaurant_id=Restaurant.restaurant_id AND restaurant_open=1 AND CategoryDish.dish_id=Dish.dish_id " +
                    " AND Dish.dish_id=(" +
                                        " SELECT dish_id FROM CategoryDish WHERE categorydish_id=" + dishId +
                                        ")";

            if (districtId > 0)
                sqlGetCategoryDishSameList += " AND district_id=" + districtId.ToString();

            if (consumerDistrictId > 0)
                sqlGetCategoryDishSameList += " AND " + consumerDistrictId.ToString() + " IN " +
                                                "(SELECT district_id " +
                                                "FROM ServedDistrict,CategoryDish " +
                                                "WHERE categorydish_id=" + dishId +
                                                " AND CategoryDish.restaurant_id=ServedDistrict.restaurant_id)";

            sqlGetCategoryDishSameList += " ORDER BY categorydish_score, categorydish_price";


            DataSet ds = DAL.DBHelper.getDataSet(sqlGetCategoryDishSameList);

            if (ds == null || ds.Tables.Count != 1)
            {
                return null;
            }

            return ds;
        }

        public static DataSet getCategoryDishInfo(String restaurantDishId)
        {
            String sqlGetCategoryDishInfo =
                "SELECT * from CategoryDish WHERE categorydish_id=" + restaurantDishId;

            DataSet ds = DAL.DBHelper.getDataSet(sqlGetCategoryDishInfo);

            if (ds == null || ds.Tables.Count != 1)
            {
                return null;
            }

            return ds;
        }

        public static List<Dish> DishesWithSimilarName(String dishName)
        {
            SqlParameter[] sqlParams = { 
                new SqlParameter("dishName", SqlDbType.VarChar)};
            sqlParams[0].Value = dishName;

            DataSet ds = DAL.DBHelper.getDataSetPro("SP_DishesWithSimilarName", sqlParams);

            List<Dish> dishes = new List<Dish>();
            if (null != ds)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    Int16 dishId = Convert.ToInt16(ds.Tables[0].Rows[i].ItemArray[0].ToString());
                    String gotDishName = ds.Tables[0].Rows[i].ItemArray[1].ToString();
                    String dishDesc = ds.Tables[0].Rows[i].ItemArray[2].ToString();
                    Dish dish = new Dish();
                    dish.Id = dishId;
                    dish.Name = gotDishName;
                    dish.Description = dishDesc;
                    dishes.Add(dish);
                }
            }

            return dishes;
        }

        public static DataSet SearchDishes(String keywords, int districtId, Int16 servedDistrictId)
        {
            SqlParameter[] sqlParams = { 
            new SqlParameter("KeyWords", SqlDbType.VarChar),
            new SqlParameter("RestaurantDistrictId", SqlDbType.SmallInt),
            new SqlParameter("CustomerDistrictId", SqlDbType.SmallInt)};
            sqlParams[0].Value = keywords;
            sqlParams[1].Value = districtId;
            sqlParams[2].Value = servedDistrictId;

            DataSet ds = DAL.DBHelper.getDataSetPro("[SP_SearchDishes]", sqlParams);

            if (ds == null || ds.Tables.Count != 1)
            {
                return null;
            }

            return ds;
        }
    }
}
